# 公司部门表添加、查询数据

from sys import path
from flask import Blueprint, jsonify, request, make_response
from flask_restful import Api, Resource
from xlsxwriter.utility import quote_sheetname
from settings.mysqlM import db
from settings.dataM import wuxian
from settings.fileM import myf
import os
from xlrd import open_workbook
from io import BytesIO
import xlsxwriter
from urllib.parse import quote
import datetime
import mimetypes


dept = Blueprint('deptdp', __name__)
api = Api(dept)


class Dept(Resource):
    def get(self):
        sql = "select * from depts"
        res = db.find_all(sql)
        res1 = wuxian(res)
        return jsonify({'code': 200, 'list': res1})


# 导入
class Daoru(Resource):
    def post(self):
        # # file = request.files['file']
        # # newname = myf.rename(file.filename)
        # # file.save(os.path.join('./static/files/' + newname))
        # # # 打开文件读取
        # # name = './static/files/'+newname
        # # bk = open_workbook(name,encoding_override='utf8')
        # # sh = bk.sheets()[0]
        # # nrow = sh.nrows
        # # ncols = sh.ncols
        # # for i in range(1, nrow):
        # #     row_date = sh.row_values(i)
        # #     for j in range(0, ncols):
        # #         print(row_date[j])
        # # return 'ok'
        # file = request.files['file']


        # newname = myf.rename(file.filename)
        # # 上传文件
        # file.save(os.path.join('./static/files/'+newname))
        # # 打开文件读取
        # # name = './static/files/'+newname
        name='./static/files/' + '54a79c986aa64036aea62c17adcfdeca.xls'
        bk = open_workbook(name, encoding_override='utf-8')
        sh = bk.sheets()[0]
        nrows = sh.nrows
        ncols = sh.ncols
        for i in range(1, nrows):
            row_date = sh.row_values(i)
            for j in range(0, ncols):
                print(row_date[j])
        return jsonify({'code': 200, 'msg': '成功'})    


class Down(Resource):
    def get(self):
        out = BytesIO()
        workbook = xlsxwriter.Workbook(out)
        table = workbook.add_worksheet()
        table.write(0, 0, '班级')
        table.write(0, 1, '讲师')
        workbook.close()
        filename = quote("班级列表_{}.xls".format(
            datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
        file = make_response(out.getvalue())
        out.close()
        mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
        file.headers['Content-Type'] = mime_type
        file.headers["Cache-Control"] = "no-cache"
        file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(
            filename)
        return file


api.add_resource(Dept, '/dept')
api.add_resource(Daoru, '/upload')
